using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace DAL
{
    public class Threads : DAL.DataManager<Thread>
    {
        private static Threads Instance = new Threads();
        static Threads() { }

        public static string GetTableName()
        {
            return Instance.TableName;
        }

        public override string TableName
        {
            get { return @"yboard_threads"; }
        }

        protected override void CopyToItem(DataRow Row, Thread Item)
        {
            Item.Forum = Forums.Item(Convert.ToInt64(Row["ForumID"]));

            switch (Convert.ToInt32(Row["Priority"]))
            {
                case 1:
                    Item.Priority = Thread.ThreadPriority.Sticky;
                    break;
                default:
                    Item.Priority = Thread.ThreadPriority.Normal;
                    break;
            }


            Item.FirstPost = Posts.GetFirstPostByThread(Item);
            if (Row.Table.Columns.Contains("LastPostID") &&
                Row["LastPostID"] != DBNull.Value)
            {
                Item.LastPost = Posts.Item(Convert.ToInt64(Row["LastPostID"]));
            }
            else
            {
                Item.LastPost = Posts.GetLastPostByThread(Item);
            }
            Item.TotalReplies = Convert.ToInt32(Row["TotalReplies"]);
            Item.TotalViews = Convert.ToInt32(Row["TotalViews"]);

            switch (Convert.ToInt32(Row["Status"]))
            {
                case 0:
                    Item.Status = Thread.ThreadStatus.Locked;
                    break;
                case -1:
                    Item.Status = Thread.ThreadStatus.Deleted;
                    break;
                case 1:
                    Item.Status = Thread.ThreadStatus.Open;
                    break;
            }
        }

        protected override void CopyFromItem(DataManagerQuery query, Thread Item)
        {
            query.AddParam("ForumID", Item.Forum.ID);

            switch (Item.Priority)
            {
                case Thread.ThreadPriority.Sticky:
                    query.AddParam("Priority", 1);
                    break;
                default:
                    query.AddParam("Priority", 0);
                    break;
            }

            query.AddParam("TotalViews", Item.TotalViews);
            query.AddParam("TotalReplies", Item.TotalReplies);

            switch (Item.Status)
            {
                case Thread.ThreadStatus.Locked:
                    query.AddParam("Status", 0);
                    break;
                case Thread.ThreadStatus.Deleted:
                    query.AddParam("Status", -1);
                    break;
                default:
                    query.AddParam("Status", 1);
                    break;
            }
        }

        public static Thread Item(Int64 id)
        {
            return Instance.GetItem(id);
        }

        public static List<Thread> All()
        {
            return Instance.GetItems();
        }

        private static void AddStatusParam(DataManagerQuery query)
        {
            if (Managers.UserLoginManager.CurrentUser == null ||
                Managers.UserLoginManager.CurrentUser.Status != User.UserStatus.Banned)
            {
                query.AddParam("Status", Comparison.GreaterThanOrEqual, 0);
            }
        }

        public static void Add(Thread Item)
        {
            if (Item.Forum == null)
            {
                throw new ThreadNullForumException("Cannot create a new thread without a parent forum.");
            }
            Item.Forum.TotalThreads += 1;
            Item.Forum.TotalPosts += 1;
            Forums.Save(Item.Forum);
            Item.Status = Thread.ThreadStatus.Open;
            Item.Priority = Thread.ThreadPriority.Normal;

            Instance.Insert(Item);
        }

        public static void Save(Thread Item)
        {
            Instance.Update(Item);
        }

        private static string LastPostIDSql()
        {
            return string.Format("(SELECT top 1 p.ID FROM {0} p WHERE p.ThreadID = t.ID AND p.Visible = 1 ORDER BY p.ID Desc) as LastPostID\n", Posts.GetTableName()); ;
        }

        public static List<Thread> GetByForum(Forum parentForum)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.*, ");
            sb.AppendLine(LastPostIDSql());
            sb.AppendFormat("FROM {0} t\n", Instance.TableName);
            sb.AppendLine("WHERE t.ForumID = @forumID");
            sb.AppendLine("ORDER BY t.Priority Desc");
            sb.AppendLine("LastPostID DESC");

            SqlCommand comm = new SqlCommand(sb.ToString());
            comm.Parameters.AddWithValue("@forumID", parentForum.ID);
            return Instance.GetBySqlCommand(comm);
        }

        public static DataManagerPage<Thread> GetByForumAndPage(Forum parentForum, int page)
        {
            int min = ((page - 1) * 25) + 1;
            int max = (page * 25);

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("With Items (ID, ForumID, Priority, Status, TotalReplies, TotalViews, LastPostID, RowNumber) as");
            sb.AppendLine("(");
            sb.AppendLine("	SELECT *, Row_Number() OVER (ORDER BY LastPostID DESC) as RowNumber FROM");
            sb.AppendLine("	(");
            sb.AppendFormat("		SELECT TOP {0} t.ID, t.ForumID, t.Priority, t.Status, t.TotalReplies, T.TotalViews, \n", max);
            sb.AppendLine(LastPostIDSql());
            sb.AppendFormat("		FROM {0} t\n", GetTableName());
            sb.AppendLine("		WHERE t.ForumID = @forumID AND t.Priority = 0 AND t.Status = 1");
            sb.AppendLine("		ORDER BY LastPostID DESC");
            sb.AppendLine(" ) as temp");
            sb.AppendLine(")");
            sb.AppendFormat("SELECT * FROM Items WHERE RowNumber between {0} and {1}", min, max);

            SqlCommand comm = new SqlCommand(sb.ToString());
            comm.Parameters.AddWithValue("@forumID", parentForum.ID);

            DataManagerPage<Thread> myItems = new DataManagerPage<Thread>(25, Instance.GetBySqlCommand(comm));

            if (page == 1)
            {
                List<Thread> threadsWithPriority = GetByForumAndPriority(parentForum, Thread.ThreadPriority.Sticky);

                List<Thread> allThreads = new List<Thread>(myItems.Items.Count + threadsWithPriority.Count);
                allThreads.AddRange(threadsWithPriority);
                allThreads.AddRange(myItems.Items);

                myItems.Items = allThreads;
            }

            return myItems;
        }

        public static List<Thread> GetByForumAndPriority(Forum parentForum, Thread.ThreadPriority priority)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.*, ");
            sb.AppendLine(LastPostIDSql());
            sb.AppendFormat("FROM {0} t\n", Instance.TableName);
            sb.AppendLine("WHERE t.ForumID = @forumID AND t.Priority = @priority");
            sb.AppendLine("ORDER BY ");
            sb.AppendLine("LastPostID DESC");

            SqlCommand comm = new SqlCommand(sb.ToString());
            comm.Parameters.AddWithValue("@forumID", parentForum.ID);
            switch(priority)
            {
                case Thread.ThreadPriority.Sticky:
                    comm.Parameters.AddWithValue("@priority", 1);
                    break;
                default:
                    comm.Parameters.AddWithValue("@priority", 0);
                    break;
            }

            return Instance.GetBySqlCommand(comm);
        }

        public static int GetTotalThreads()
        {
            DataManagerQuery query = new DataManagerQuery();
            AddStatusParam(query);
            return Instance.GetCount(query);
        }

        public static Thread GetLastThreadByForum(Forum parentForum)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT top 1 t.* ");
            sb.AppendFormat("FROM {0} t\n", Instance.TableName).AppendLine();
            sb.AppendFormat("INNER JOIN {0} p on p.ThreadID = t.ID\n", Posts.GetTableName());
            sb.AppendLine("WHERE t.ForumID = @forumID AND");
            sb.AppendLine("p.Visible = 1");
            sb.AppendLine("ORDER BY ");
            sb.AppendLine("p.ID DESC");

            SqlCommand comm = new SqlCommand(sb.ToString());
            comm.Parameters.AddWithValue("@forumID", parentForum.ID);

            List<Thread> myThread = Instance.GetBySqlCommand(comm);
            if (myThread.Count > 0)
            {
                return myThread[0];
            }
            else
            {
                return null;
            }
        }

        public static List<Thread> Search(Helpers.ForumSearchHelper search, int page)
        {
            search.DisplayMode = Helpers.ForumSearchHelper.Display.AsThreads;
            SqlCommand comm = search.GenerateSearchSqlCommand(page);
            return Instance.GetBySqlCommand(comm);
        }
    }

    public class Thread : DAL.DataManagerRow
    {
        public enum ThreadStatus
        {
            Open,
            Locked,
            Deleted
        }

        public enum ThreadPriority
        {
            Normal,
            Sticky
        }

        private Forum _Forum;
        public Forum Forum
        {
            get
            {
                return _Forum;
            }
            set
            {
                _Forum = value;
            }
        }

        private int _totalReplies;
        public int TotalReplies
        {
            get
            {
                return _totalReplies;
            }
            set
            {
                _totalReplies = value;
            }
        }

        public int TotalPages
        {
            get
            {
                return (int)Math.Ceiling((double)((TotalReplies + 1) / 25.0));
            }
        }

        private int _totalViews;
        public int TotalViews
        {
            get
            {
                return _totalViews;
            }
            set
            {
                _totalViews = value;
            }
        }

        private ThreadPriority _Priority;
        public ThreadPriority Priority
        {
            get
            {
                return _Priority;
            }
            set
            {
                _Priority = value;
            }
        }


        private Post _FirstPost;
        public Post FirstPost
        {
            get
            {
                return _FirstPost;
            }
            set
            {
                _FirstPost = value;
            }
        }


        private Post _LastPost;
        public Post LastPost
        {
            get
            {
                return _LastPost;
            }
            set
            {
                _LastPost = value;
            }
        }

        private ThreadStatus _Status;
        public ThreadStatus Status
        {
            get
            {
                return _Status;
            }
            set
            {
                _Status = value;
            }
        }
    }
}